package com.mag.service;

import com.mag.domain.Suppliers;
import com.mag.util.SqlHelper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.text.SimpleDateFormat;

public class SuppliersService {
    //判断人员是否存在
    public Suppliers CheckSupplier(Suppliers supplier) {
        String sql = "select * from supplier where SupplierID=?";
        String[] parameters = {supplier.getSupplierID() + ""};
        ResultSet rs = SqlHelper.executeQuery(sql, parameters);

        try {
            if (rs.next()) {
                supplier.setSupplierID(rs.getString(1));
                supplier.setSupplierName(rs.getString(2));
                supplier.setSupplierAddress(rs.getString(3));
                supplier.setWebSite(rs.getString(4));
                supplier.setSupplierPhone(rs.getString(5));
                supplier.setFaxSupplier(rs.getString(6));
                supplier.setOfficialEmail(rs.getString(7));
                supplier.setPersonContact(rs.getString(8));
                supplier.setPhoneContact(rs.getString(9));
                supplier.setDateBulid(rs.getDate(10));
                supplier.setGoodsSupply(rs.getString(11));
                supplier.setDateFirstSupply(rs.getDate(12));
                supplier.setAccumulativeTime(rs.getInt(13));
                supplier.setAccumulativeLevel(rs.getInt(14));
                supplier.setNumFault(rs.getInt(15));
                supplier.setNumBacklogging(rs.getInt(16));
                supplier.setTotalMoney(rs.getDouble(17));
                supplier.setNoFaultRate(rs.getDouble(18));
                supplier.setDeliveryOnTime(rs.getDouble(19));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //反向引用,关闭资源
            SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());

        }
        return supplier;
    }

    //得到所有成员
    public ArrayList<Suppliers> GetAllSuppliers() {
        ArrayList<Suppliers> al = new ArrayList<Suppliers>();
        String sql = "select * from supplier";
        String[] parameters = {};
        ResultSet rs = SqlHelper.executeQuery(sql, parameters);

        try {
            while (rs.next()) {
                Suppliers supplier = new Suppliers();
                supplier.setSupplierID(rs.getString(1));
                supplier.setSupplierName(rs.getString(2));
                supplier.setSupplierAddress(rs.getString(3));
                supplier.setWebSite(rs.getString(4));
                supplier.setSupplierPhone(rs.getString(5));
                supplier.setFaxSupplier(rs.getString(6));
                supplier.setOfficialEmail(rs.getString(7));
                supplier.setPersonContact(rs.getString(8));
                supplier.setPhoneContact(rs.getString(9));
                supplier.setDateBulid(rs.getDate(10));
                supplier.setGoodsSupply(rs.getString(11));
                supplier.setDateFirstSupply(rs.getDate(12));
                supplier.setAccumulativeTime(rs.getInt(13));
                supplier.setAccumulativeLevel(rs.getInt(14));
                supplier.setNumFault(rs.getInt(15));
                supplier.setNumBacklogging(rs.getInt(16));
                supplier.setTotalMoney(rs.getDouble(17));
                supplier.setNoFaultRate(rs.getDouble(18));
                supplier.setDeliveryOnTime(rs.getDouble(19));
                al.add(supplier);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
        }
        return al;
    }

    //根据用户名查询成员
    public Suppliers GetSupplierBySupplierID(String id) {
        Suppliers supplier = new Suppliers();
        String sql = "select * from supplier where SupplierID=?";
        String[] parameters = {id};                                  //个人隐私，精准查询
        ResultSet rs = SqlHelper.executeQuery(sql, parameters);

        try {
            if (rs.next()) {
                supplier.setSupplierID(rs.getString(1));
                supplier.setSupplierName(rs.getString(2));
                supplier.setSupplierAddress(rs.getString(3));
                supplier.setWebSite(rs.getString(4));
                supplier.setSupplierPhone(rs.getString(5));
                supplier.setFaxSupplier(rs.getString(6));
                supplier.setOfficialEmail(rs.getString(7));
                supplier.setPersonContact(rs.getString(8));
                supplier.setPhoneContact(rs.getString(9));
                supplier.setDateBulid(rs.getDate(10));
                supplier.setGoodsSupply(rs.getString(11));
                supplier.setDateFirstSupply(rs.getDate(12));
                supplier.setAccumulativeTime(rs.getInt(13));
                supplier.setAccumulativeLevel(rs.getInt(14));
                supplier.setNumFault(rs.getInt(15));
                supplier.setNumBacklogging(rs.getInt(16));
                supplier.setTotalMoney(rs.getDouble(17));
                supplier.setNoFaultRate(rs.getDouble(18));
                supplier.setDeliveryOnTime(rs.getDouble(19));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //反向引用,关闭资源
            SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());

        }
        return supplier;
    }

    //根据姓名查询成员
    public ArrayList<Suppliers> GetSupplierBySupplierName(String name) {
        ArrayList<Suppliers> al = new ArrayList<Suppliers>();
        String sql = "select * from supplier where SupplierName like ?";
        String[] parameters = {"%" + name + "%"};                        //模糊查询
        ResultSet rs = SqlHelper.executeQuery(sql, parameters);

        try {
            while (rs.next()) {
                Suppliers supplier = new Suppliers();
                supplier.setSupplierID(rs.getString(1));
                supplier.setSupplierName(rs.getString(2));
                supplier.setSupplierAddress(rs.getString(3));
                supplier.setWebSite(rs.getString(4));
                supplier.setSupplierPhone(rs.getString(5));
                supplier.setFaxSupplier(rs.getString(6));
                supplier.setOfficialEmail(rs.getString(7));
                supplier.setPersonContact(rs.getString(8));
                supplier.setPhoneContact(rs.getString(9));
                supplier.setDateBulid(rs.getDate(10));
                supplier.setGoodsSupply(rs.getString(11));
                supplier.setDateFirstSupply(rs.getDate(12));
                supplier.setAccumulativeTime(rs.getInt(13));
                supplier.setAccumulativeLevel(rs.getInt(14));
                supplier.setNumFault(rs.getInt(15));
                supplier.setNumBacklogging(rs.getInt(16));
                supplier.setTotalMoney(rs.getDouble(17));
                supplier.setNoFaultRate(rs.getDouble(18));
                supplier.setDeliveryOnTime(rs.getDouble(19));
                al.add(supplier);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //反向引用,关闭资源
            SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());

        }
        return al;
    }

    //根据用户名查询成员数量
    public int GetCountOfSupplierByID(String supplierID) {
        int count = 0;
        String sql = "select COUNT(*) from supplier where supplierID=?";
        String[] parameters = {supplierID + ""};
        ResultSet rs = SqlHelper.executeQuery(sql, parameters);

        try {
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //反向引用,关闭资源
            SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());

        }

        return count;
    }

    public boolean UpdateSupplier(Suppliers supplier) {
        boolean b = true;
        String sql = "update supplier set SupplierName=?,SupplierAddress=?,WebSite=?,SupplierPhone=?,FaxSupplier=?,OfficialEmail=?,PersonContact=?,PhoneContact=?,DateBulid=?,GoodsSupply=?,DateFirstSupply=?,AccumulativeTime=?,AccumulativeLevel=?,NumFault=?,NumBacklogging=?,TotalMoney=?,NoFaultRate=?,DeliveryOnTime=? where SupplierID=?";

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateBulid = sdf.format(supplier.getDateBulid());
        String dateFirstSupply = sdf.format(supplier.getDateFirstSupply());

        String[] parameters = {supplier.getSupplierName(), supplier.getSupplierAddress(), supplier.getWebSite(), supplier.getSupplierPhone(), supplier.getFaxSupplier(), supplier.getOfficialEmail(), supplier.getPersonContact(), supplier.getPhoneContact(), dateBulid, supplier.getGoodsSupply(), dateFirstSupply, supplier.getAccumulativeTime() + "", supplier.getAccumulativeLevel() + "", supplier.getNumFault() + "", supplier.getNumBacklogging() + "", supplier.getTotalMoney() + "", supplier.getNoFaultRate() + "", supplier.getDeliveryOnTime() + "", supplier.getSupplierID() + "",};

        try {
            SqlHelper.executeUpdate(sql, parameters);
        } catch (Exception e) {
            e.printStackTrace();
            b = false;
        }

        return b;
    }

    //通过用户名删除成员
    public boolean DelSupplier(String id) {
        boolean b = true;
        String sql = "delete from supplier where SupplierID=?";
        String[] parameters = {id};

        try {
            SqlHelper.executeUpdate(sql, parameters);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            b = false;
        }
        return b;
    }

    //新增成员
    public boolean AddSupplier(Suppliers supplier) {
        boolean b = true;
        String sql = "insert into supplier(SupplierID,SupplierName,SupplierAddress,WebSite,SupplierPhone,FaxSupplier," + "OfficialEmail,PersonContact,PhoneContact,DateBulid,GoodsSupply,DateFirstSupply,AccumulativeTime,AccumulativeLevel,NumFault," + "NumBacklogging,TotalMoney,NoFaultRate,DeliveryOnTime) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateBulid = sdf.format(supplier.getDateBulid());
        String dateFirstSupply = sdf.format(supplier.getDateFirstSupply());

        String[] parameters = {supplier.getSupplierID() + "", supplier.getSupplierName() + "", supplier.getSupplierAddress() + "", supplier.getWebSite() + "", supplier.getSupplierPhone() + "", supplier.getFaxSupplier() + "", supplier.getOfficialEmail() + "", supplier.getPersonContact() + "", supplier.getPhoneContact() + "", dateBulid, supplier.getGoodsSupply() + "", dateFirstSupply, supplier.getAccumulativeTime() + "", supplier.getAccumulativeLevel() + "", supplier.getNumFault() + "", supplier.getNumBacklogging() + "", supplier.getTotalMoney() + "", supplier.getNoFaultRate() + "", supplier.getDeliveryOnTime() + ""};

        try {
            SqlHelper.executeUpdate(sql, parameters);
        } catch (Exception e) {
            e.printStackTrace();
            b = false;
        }

        return b;
    }

}